package com.kaki.mdb;

import org.springframework.core.io.ClassPathResource;

import javax.swing.*;
import javax.swing.plaf.basic.BasicButtonUI;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;
import javax.swing.text.*;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.TreeNode;
import javax.swing.tree.TreePath;
import java.awt.*;
import java.awt.event.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.List;

/**
 * @author Kaki Nakajima
 * @description DB Show 核心控制类
 * @date 2024-09-17 10:11
 */
public class DBShowJDBCHandler implements ActionListener{

    private static Connection connection = null;

    private static  DBAccess dao = null;

    private static Color themeColor = new Color(204,238,200);

    private static Font FONT_20 = new Font("微软雅黑",Font.LAYOUT_NO_LIMIT_CONTEXT,20);
    private static Font FONT_14 = new Font("微软雅黑",Font.LAYOUT_NO_LIMIT_CONTEXT,14);
    private static Font FONT_18 = new Font("微软雅黑",Font.LAYOUT_NO_LIMIT_CONTEXT,18);
    private static Font FONT_16 = new Font("微软雅黑",Font.LAYOUT_NO_LIMIT_CONTEXT,16);

    private static Map<String,Object> DBCONNECTIONM_MAP = null;

    private static String MDB_PATH = null;

    private static String DB_TYPE = "";

    // 查询耗时
    private static Long QRY_COST_TIME = 0L;

    // 查询结果的总条数
    private static Integer TOTAL = 0;

    private static String textArea = "";

    // 提示
    public static void DBShowStrat(Map<String,Object> dbConnectionMap) throws IOException {
        MDB_PATH = (String) dbConnectionMap.get("path");
        DB_TYPE = (String) dbConnectionMap.get("dbType");

        // 总控
        final DBShowJFrame window1 = new DBShowJFrame("DB Show 7.0 " + "[" + MDB_PATH + "]" + " DB type" + "[" + DB_TYPE + "]");

        Container container = window1.getContentPane();

        container.setBackground(themeColor);

        // 总盒子 为水平盒子 采用两列布局形式
        Box mainBox = Box.createHorizontalBox();

        // 盒子组 leftBox 为水平盒子
        Box leftBox = Box.createHorizontalBox();
        leftBox.setBounds(265,200,100,50);

        Box leftUpBox = Box.createVerticalBox();
        Box leftChildBox1 = Box.createHorizontalBox();
        Box leftChildBox2 = Box.createHorizontalBox();
        Box leftDownBox = Box.createHorizontalBox();

        // rightBox 为垂直盒子
        Box rightBox = Box.createVerticalBox();
        rightBox.setBounds(265,200,100,900);

        //************************
        // 两个里边盒子上下排列为水平盒子
        Box toolBarBox = Box.createHorizontalBox();  // 工具栏盒子

        Box qryBox = Box.createHorizontalBox();  // 查询条件盒子
        Box resultBox = Box.createHorizontalBox(); // 结果展示盒子
        Box totalNumBox = Box.createHorizontalBox();  // 条数展示盒子

        // 文本域监听器
        DBShowPlainDocument myPlainDocument = new DBShowPlainDocument(textArea);

        final JTextPane qryArea = new JTextPane();
        qryArea.setFont(FONT_20);
        qryBox.add(new JScrollPane(qryArea){
            @Override
            public Dimension getPreferredSize(){
                return new Dimension(100,100);
            }
        });

        // 表格组
        final DefaultTableModel tableModel = new DefaultTableModel();

        final JTable jTable = new JTable(tableModel);
        // jTable.setRowHeight(1000);

        jTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
        jTable.getTableHeader().setBackground(new Color(76,170,249));
        jTable.getTableHeader().setFont(new Font("微软雅黑",Font.BOLD,14));
        jTable.getTableHeader().setForeground(new Color(245,246,249));
        jTable.setSelectionBackground(new Color(184,215,246));
        jTable.getTableHeader().setReorderingAllowed(false);// 单元格不可拖动

        jTable.setFont(FONT_14);
        jTable.setRowHeight(20);

        // 工具栏 按钮组
        final JButton excuButton = new JButton("执行");
        ImageIcon imageIcon = new ImageIcon(new ClassPathResource("/photos/main.png").getURL());
        excuButton.setIcon(imageIcon);
        excuButton.setContentAreaFilled(false);
        excuButton.setUI(new BasicButtonUI());
        excuButton.setFont(FONT_14);

        JButton showGoodButton = new JButton("高亮");
        showGoodButton.setFont(FONT_14);
        ImageIcon meihuaIcon = new ImageIcon(new ClassPathResource("/photos/liang.png").getURL());
        showGoodButton.setIcon(meihuaIcon);
        showGoodButton.setContentAreaFilled(false);
        showGoodButton.setUI(new BasicButtonUI());

        JButton openNewDateSource = new JButton("打开一个新的数据源");
        openNewDateSource.setFont(FONT_14);
        ImageIcon openNewDataSourceIcon = new ImageIcon(new ClassPathResource("/photos/opennewdatasource.png").getURL());
        openNewDateSource.setIcon(openNewDataSourceIcon);
        openNewDateSource.setContentAreaFilled(false);
        openNewDateSource.setUI(new BasicButtonUI());

        JButton saveSql = new JButton("保存数据脚本");
        saveSql.setFont(FONT_14);
        ImageIcon saveSqlPng = new ImageIcon(new ClassPathResource("/photos/save.png").getURL());
        saveSql.setIcon(saveSqlPng);
        saveSql.setContentAreaFilled(false);
        saveSql.setUI(new BasicButtonUI());

        JButton exportAllData = new JButton("导出全部数据");
        exportAllData.setFont(FONT_14);
        exportAllData.setIcon(saveSqlPng);
        exportAllData.setContentAreaFilled(false);
        exportAllData.setUI(new BasicButtonUI());

        // 数据结果条数据框
        final JLabel totalTimeLabel = new JLabel("耗时 " + QRY_COST_TIME + " 秒");
        totalTimeLabel.setFont(FONT_18);

        final JLabel totalLabel = new JLabel("耗时 " + TOTAL + " 秒");
        totalLabel.setFont(FONT_18);

        // 树形节点组
        // 第一层
        DefaultMutableTreeNode firstTree = new DefaultMutableTreeNode("数据库列表");

        // 树形结构组
        final JTree jtree = new JTree(firstTree);
        jtree.setBounds(160,200,100,800);
        jtree.setRowHeight(30);
        jtree.setBackground(themeColor);
        jtree.setFont(FONT_18);

        jtree.addMouseListener(new MouseListener() {

            @Override
            public void mouseClicked(MouseEvent e) {
                TreePath path = jtree.getPathForLocation(e.getX(), e.getY());

                if (path != null){
                    TreeNode node = (TreeNode) path.getLastPathComponent();
                    String doubleClickedTableName = node.toString();

                    // 监听表名双击事件
                    int clickCount = e.getClickCount();

                    if (clickCount == 2){
                        System.out.println("点击次数："+clickCount);

                        String qryBoxData = qryArea.getText();

                        if (qryBoxData == null){
                            qryBoxData = "";
                        }

                        // 将双击表名拼接到 查询框数据 的末尾
                        qryArea.setText(qryBoxData+doubleClickedTableName);
                    }
                }
            }

            @Override
            public void mousePressed(MouseEvent e) {

            }

            @Override
            public void mouseReleased(MouseEvent e) {

            }

            @Override
            public void mouseEntered(MouseEvent e) {

            }

            @Override
            public void mouseExited(MouseEvent e) {

            }
        });

        // 下拉框组
        final JPanel themePanel = new JPanel();
        themePanel.setBackground(themeColor);
        themePanel.setSize(40,20);

        JLabel currentThemeColor = new JLabel("主题颜色");
        currentThemeColor.setFont(FONT_18);

        final JComboBox themeSelector = new JComboBox();
        themeSelector.addItem("--请选择--");
        themeSelector.addItem("经典红");
        themeSelector.addItem("经典绿");
        themeSelector.addItem("经典蓝");
        themeSelector.addItem("淑銮专用");
        themeSelector.addItem("sqlYong主题");

        themePanel.add(themeSelector);
        themeSelector.setSize(80,20);
        themeSelector.setFont(FONT_16);

        // 主题下拉框监听器
        themeSelector.addItemListener(new ItemListener() {

            @Override
            public void itemStateChanged(ItemEvent e) {
                if (e.getStateChange() == ItemEvent.SELECTED){
                    String selectedItem = themeSelector.getSelectedItem().toString();

                    if ("经典红".equals(selectedItem)){
                        themeColor = new Color(238,0,69);

                        Container container = window1.getContentPane();
                        container.setBackground(themeColor);
                        themePanel.setBackground(themeColor);
                        jtree.setBackground(themeColor);
                    }else if ("经典绿".equals(selectedItem)){
                        themeColor = new Color(238,238,200);

                        Container container = window1.getContentPane();
                        container.setBackground(themeColor);
                        themePanel.setBackground(themeColor);
                        jtree.setBackground(themeColor);
                    }else if ("经典蓝".equals(selectedItem)){
                        themeColor = new Color(0,234,238);

                        Container container = window1.getContentPane();
                        container.setBackground(themeColor);
                        themePanel.setBackground(themeColor);
                        jtree.setBackground(themeColor);
                    }else if ("淑銮专用".equals(selectedItem)){
                        themeColor = new Color(76,170,249);

                        Container container = window1.getContentPane();
                        container.setBackground(themeColor);
                        themePanel.setBackground(themeColor);
                        jtree.setBackground(themeColor);
                    }else if ("sqlYong主题".equals(selectedItem)){
                        themeColor = new Color(3,188,238);

                        Container container = window1.getContentPane();
                        container.setBackground(themeColor);
                        themePanel.setBackground(themeColor);
                        jtree.setBackground(themeColor);
                    }
                }
            }
        });

        leftChildBox1.add(currentThemeColor);
        leftChildBox2.add(themePanel);

        leftUpBox.add(leftChildBox1,BorderLayout.WEST);
        leftUpBox.add(leftChildBox2,BorderLayout.EAST);

        showGoodButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {

                try{
                    final Document document = qryArea.getDocument();

                    final SimpleAttributeSet simpleAttributeSetBlack = new SimpleAttributeSet();

                    StyleConstants.setForeground(simpleAttributeSetBlack,Color.BLACK);
                    StyleConstants.setFontSize(simpleAttributeSetBlack,20);

                    final SimpleAttributeSet simpleAttributeSetGreen = new SimpleAttributeSet();

                    StyleConstants.setForeground(simpleAttributeSetGreen,new Color(58,128,128));
                    StyleConstants.setFontSize(simpleAttributeSetGreen,20);

                    final SimpleAttributeSet simpleAttributeSetRed = new SimpleAttributeSet();

                    StyleConstants.setForeground(simpleAttributeSetRed,new Color(255,0,0));
                    StyleConstants.setFontSize(simpleAttributeSetRed,20);

                    final SimpleAttributeSet simpleAttributeSetMulasaki = new SimpleAttributeSet();

                    StyleConstants.setForeground(simpleAttributeSetMulasaki,new Color(151,94,255));
                    StyleConstants.setFontSize(simpleAttributeSetMulasaki,20);

                    System.out.println(document.getText(0,document.getLength()));

                    String text = document.getText(0, document.getLength());

                    String[] split = text.split("\n");

                    qryArea.setText("");

                    for (String line : split) {

                        if ((!"".equals(line)) && "---".equals(line.trim().substring(0,4))){
                            document.insertString(document.getLength(),line+"\n",simpleAttributeSetRed);
                        }else if ((!"".equals(line)) && "--".equals(line.trim().substring(0,4))){
                            document.insertString(document.getLength(),line+"\n",simpleAttributeSetGreen);
                        }else {
                            List<String> keywordList = new ArrayList<String>();

                            keywordList.add("select");
                            keywordList.add("from");
                            keywordList.add("where");
                            keywordList.add("in");
                            keywordList.add("delete");
                            keywordList.add("insert");
                            keywordList.add("into");
                            keywordList.add("values");
                            keywordList.add("update");
                            keywordList.add("set");

                            if (!"".equals(line)){
                                String[] keywordsArr = line.split(" ");

                                for (String keyword : keywordsArr) {
                                    if (keywordList.contains(keyword)){
                                        document.insertString(document.getLength(),keyword+" ",simpleAttributeSetMulasaki);
                                    }else {
                                        document.insertString(document.getLength(),keyword+" ",simpleAttributeSetBlack);
                                    }
                                }

                                document.insertString(document.getLength()," \n",simpleAttributeSetBlack);
                            }else {
                                document.insertString(document.getLength(),line+"\n",simpleAttributeSetBlack);
                            }
                        }
                    }

                }catch (Exception ex){
                    System.out.println("======2");
                    ex.printStackTrace();
                }
            }
        });

        // 执行按钮监听器
        excuButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String selectedText = qryArea.getSelectedText();

                System.out.println("鼠标选中的值："+selectedText);

                try {
                    String sql = selectedText.toLowerCase();

                    if (sql.startsWith("select") || sql.contains("select") || sql.contains("show")){

                        long startTime = System.currentTimeMillis();
                        jTable.updateUI();
                        DBAccess dao = getConnectionDao();

                        ResultSet resultSet = dao.excuteQuery(selectedText);

                        ResultSetMetaData metaData = resultSet.getMetaData();

                        int columnCount = metaData.getColumnCount();

                        // 填充表头
                        Object columnNameArray = new Object[columnCount];

                        List<Map<String, Object>> tableData = new ArrayList<Map<String, Object>>();
                        List<String> tableDataChild = new ArrayList<String>();
                        List<String> lieList = new ArrayList<String>();

                        Vector<Object[]> dataValueList = new Vector(0);
                        LinkedHashSet<String> columSet = new LinkedHashSet<String>(0);

                        while (resultSet.next()){
                            Map<String, Object> childMap = new HashMap<String, Object>();
                            Object[] tempObject = new Object[columnCount];

                            for (int i=1;i<=columnCount;i++){
                                String columnName = metaData.getColumnLabel(i);
                                columSet.add(columnName);

                                String string = resultSet.getString(columnName);

                                //填充表数据
                                tempObject[i-1] = string == null?"(NULL)":string;
                                childMap.put(columnName,string);
                            }
                            dataValueList.add(tempObject);
                            tableData.add(childMap);
                        }
                        tableModel.setColumnCount(0);
                        tableModel.setRowCount(0);

                        String s = "";

                        if (tableData.size() > 0){
                            Map<String,Object> oneMap = (Map<String, Object>) tableData.get(0);

                            Set<Map.Entry<String, Object>> entries = oneMap.entrySet();

                            // 重新渲染列名
                            for (String lie : columSet) {
                                s += (", "+lie);
                                tableModel.addColumn(lie);
                                lieList.add(lie);
                                tableDataChild.add(oneMap.get(lie)+"");
                            }

                            // 列宽度（动态）设置 strat
                            TableColumnModel model0 = jTable.getColumnModel();

                            for (int i = 0;i< model0.getColumnCount();i++){
                                TableColumn column = model0.getColumn(i);

                                if (tableDataChild.get(i).length() >12){
                                    column.setPreferredWidth(tableDataChild.get(i).length() * 10);
                                }else {
                                    column.setPreferredWidth(4 * 25);
                                }
                            }
                        }

                        //列宽度（动态） 设置 end
                        System.out.println(s);

                        for (Object[] objects : dataValueList) {
                            tableModel.addRow(objects);
                        }

                        long endTime = System.currentTimeMillis();

                        TOTAL = dataValueList.size();
                        totalLabel.setText("共"+TOTAL+"条");

                        QRY_COST_TIME = (endTime-startTime) / 1000;

                        totalTimeLabel.setText("耗时 "+QRY_COST_TIME+" 秒");

                        if (TOTAL == 0){
                            totalTimeLabel.setText("耗时 "+QRY_COST_TIME+" 秒");
                            totalLabel.setText("无数据");
                        }

                    }else {
                        long startTime = System.currentTimeMillis();

                        jTable.updateUI();
                        DBAccess dao = getConnectionDao();
                        int i = dao.excuteUpdate(selectedText);

                        System.out.println("执行语句："+selectedText+"  返回结果："+i);

                        //清空表格
                        tableModel.setColumnCount(0);
                        tableModel.setRowCount(0);

                        long endTime = System.currentTimeMillis();

                        TOTAL = i;
                        totalLabel.setText("共"+TOTAL+"条");

                        QRY_COST_TIME = (endTime-startTime) / 1000;
                        totalTimeLabel.setText("耗时 "+QRY_COST_TIME+" 秒");
                    }
                }catch (Exception ex){
                    System.out.println("=========3");
                    ex.printStackTrace();
                }
            }
        });


        saveSql.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // 暂不实现
            }
        });

        // 获取所有数据库名字
        final List<String> databasesList = showDataBases();

        exportAllData.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // 导出全部数据库实现
            }
        });

        resultBox.add(new JScrollPane(jTable){

            @Override
            public Dimension getPreferredSize(){
                return new Dimension(5,5);
            }
        });

        toolBarBox.add(excuButton);
        toolBarBox.add(showGoodButton);
        toolBarBox.add(openNewDateSource);
        toolBarBox.add(saveSql);
        toolBarBox.add(exportAllData);

        // 条数盒子
        totalNumBox.add(totalTimeLabel);
        totalNumBox.add(new JLabel("  "));
        totalNumBox.add(totalLabel);

        // 填充两列布局，使右侧3分之2化
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());
        toolBarBox.add(Box.createHorizontalGlue());

        // 页面位置
        qryBox.setBounds(30,1,100,20);
        resultBox.setBounds(10,400,100,30);

        //************************
        rightBox.add(Box.createVerticalStrut(10));
        rightBox.add(toolBarBox);
        rightBox.add(Box.createVerticalStrut(10));
        rightBox.setBackground(themeColor);
        rightBox.add(qryBox);
        rightBox.add(Box.createVerticalStrut(10));
        rightBox.add(resultBox);
        rightBox.add(Box.createVerticalStrut(10));
        rightBox.add(totalNumBox);
        rightBox.add(Box.createVerticalStrut(10));

        for (String databaseName : databasesList) {
            DefaultMutableTreeNode databaseNameNode = new DefaultMutableTreeNode(databaseName);
            firstTree.add(databaseNameNode);
        }

        // 本地图片
        window1.setIconImage(new ImageIcon(new ClassPathResource("/photos/main.png").getURL()).getImage());

        leftDownBox.add(new JScrollPane(jtree){
            @Override
            public Dimension getPreferredSize(){
                return new Dimension(150,1000);
            }
        });

        leftDownBox.setBackground(new Color(60,1,1));
        leftUpBox.add(leftDownBox);
        leftBox.add(leftUpBox);


        // 嵌套盒子模型
        mainBox.add(leftBox);

        mainBox.add(rightBox);

        window1.add(mainBox);

        window1.setBounds(0,0,1920,1040);
        window1.setVisible(true);
        window1.setLayout(new BorderLayout());
        window1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        window1.validate();
    }

    private static DBAccess getConnectionDao(){
        if (dao == null){
            dao = new DBAccess(DB_TYPE,MDB_PATH);
        }
        return dao;
    }

    private static List<String> showDataBases(){
        DBAccess connectionDao = getConnectionDao();

        // 暴力破解数据库密码，需优先执行一下select * ,作为打井语句
        String getAllTableSql_init = "";

        String getAllTableSql = "";

        List<String> databasesNameList = new ArrayList<String>(0);

        //MDB
        if (DB_TYPE.equals("MDB")){
            //getAllTableSql = "SELECT CAST(null AS VARCHAR(50)) AS TABLE_CAT,CAST(null AS VARCHAR(50)) AS TABLE_SCHEM,r.TABLE_NAME AS TABLE_NAME,l.TABLE_TYPE,l.REMARKS,l.TYPE_CAT,l.TYPE_SCHEM,l.TYPE_NAME,l.SELF_REFERENCING_COL_NAME,l.REF_GENERATION,l.HSQLDB_TYPE,l.READ_ONLY,l.COMMIT_ACTION FROM INFOMATION_SCHEMA.SYSTEM_TABLE l INNER JOIN UCA_METADATA.TABLES r ON(l.TABLE_NAME = r.ESCAPED_TABLE_NAME) WHERE TABLE_CAT = 'PUBLIC' AND TABLE_SCHEM='PUBLIC' AND TABLE_NAME LIKE '%' ESCAPE '\\'";
            getAllTableSql_init = "SELECT * FROM UCA_METADATA.TABLES r";
            getAllTableSql = "SELECT CAST(null AS VARCHAR(50)) AS TABLE_CAT,CAST(null AS VARCHAR(50)) AS TABLE_SCHEM,r.TABLE_NAME AS TABLE_NAME FROM UCA_METADATA.TABLES r WHERE TABLE_CAT = 'PUBLIC' AND TABLE_SCHEM='PUBLIC' AND TABLE_NAME LIKE '%' ESCAPE '\\'";

            connectionDao.excuteQuery(getAllTableSql_init);
            ResultSet resultSet = connectionDao.excuteQuery(getAllTableSql);

            try {

                while (resultSet.next()){

                    if ("TABLE".equals(resultSet.getString(4))){
                        databasesNameList.add(resultSet.getString(3));
                    }
                }
            }catch (Exception e){
                System.out.println("=======>5");
                e.printStackTrace();
            }
        }else if (DB_TYPE.equals("Sqlite")){
            getAllTableSql = "select name from sqlite_master where type='table' order by name";

            ResultSet resultSet = connectionDao.excuteQuery(getAllTableSql);

            try {

                while (resultSet.next()){
                    databasesNameList.add(resultSet.getString(1));
                }

            }catch (Exception e){
                System.out.println("======>6");
                e.printStackTrace();
            }
        }
        return databasesNameList;
    }

    @Override
    public void actionPerformed(ActionEvent e) {

    }
}